package com.xyy.saas.payment.util;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by xuduoxiao on 2019/7/23.
 */
public class ExportExcelUtil {
    private static Logger logger = LoggerFactory.getLogger(ExportExcelUtil.class);

    /**
     * Excel文档的构成
     *
     * 在工作簿(WorkBook)里面包含了工作表(Sheet) 在工作表里面包含了行(Row) 行里面包含了单元格(Cell)
     *
     *
     * 创建一个工作簿的基本步骤
     *
     * 第一步 创建一个 工作簿 第二步 创建一个 工作表 第三步 创建一行 第四步 创建单元格 第五步 写数据 第六步
     * 将内存中生成的workbook写到文件中 然后释放资源
     *
     */
    public static XSSFWorkbook createExcelOfUsers(String[] headers, String title, String sheetName, List<String[]> dataList) throws Exception{
        XSSFWorkbook work = new XSSFWorkbook();

        //创建工作表
        Sheet sheet = work.createSheet(sheetName);

        //显示标题
        Row title_row = sheet.createRow(0);
        title_row.setHeight((short)(40*20));

//        Cell title_cell = title_row.createCell(0);

        Row header_row = sheet.createRow(0);
//        header_row.setHeight((short)(20*24));

        //创建单元格的 显示样式
        CellStyle style = work.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER); //水平方向上的对其方式
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);  //垂直方向上的对其方式


//        title_cell.setCellStyle(style);
//        title_cell.setCellValue(title);

        sheet.addMergedRegion(new CellRangeAddress(0,0,0,0));

        for(int i=0;i<headers.length;i++){
            //设置列宽   基数为256
            sheet.setColumnWidth(i, 30*256);
            Cell cell = header_row.createCell(i);
            //应用样式到  单元格上
            cell.setCellStyle(style);
            cell.setCellValue(headers[i]);
        }

        //插入需导出的数据
        for(int i=0;i<dataList.size();i++){
            Row row = sheet.createRow(i+1);
            row.setHeight((short)(20*20)); //设置行高  基数为20
            String[] data1 = dataList.get(i);
            for(int j=0;j<data1.length;j++){
//                System.out.println(data1[j].toString());
                Cell cell = row.createCell(j);
                cell.setCellValue(data1[j]);
            }
        }

//        FileOutputStream fileOut = new FileOutputStream("/Users/zhengding/Downloads/excel201804151.xlsx");
//        wb.write(fileOut);
//        fileOut.close();
        return work;
    }

    /**
     * 导出excel
     * @param response
     * @param request
     * @param excelFileName 文件名
     * @param sheetName   sheet页名
     * @param headers     表头集合
     * @param fieldNames  表头对应的实体属性集合
     * @param dataList    数据
     * @param flag        第一列是否为序号列
     * @throws IOException
     */
    public static void createExcel(HttpServletResponse response, HttpServletRequest request, String excelFileName, String sheetName, String[] headers, String[] fieldNames, List dataList, boolean flag, String[] dateFormats) throws IOException {
        OutputStream out = null;
        XSSFWorkbook work = null;
        try {
            out = response.getOutputStream();
            work = new XSSFWorkbook();
            //创建工作表
            Sheet sheet = work.createSheet(sheetName);
            //显示标题
            Row title_row = sheet.createRow(0);
            title_row.setHeight((short) (40 * 20));
            Row header_row = sheet.createRow(0);
            //创建单元格的 显示样式
            CellStyle style = work.createCellStyle();
            style.setAlignment(CellStyle.ALIGN_CENTER); //水平方向上的对其方式
            style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);  //垂直方向上的对其方式
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 0));
            int headcell_index=0;
            if(flag){//有序号
                sheet.setColumnWidth(headcell_index, 1600);
                Cell headcell = header_row.createCell(headcell_index);
                //应用样式到  单元格上
                headcell.setCellStyle(style);
                headcell.setCellValue("序号");
                headcell_index=1;
            }
            for (int i = 0; i < headers.length; i++) {
                sheet.setColumnWidth(headcell_index, 5000);
                Cell cell = header_row.createCell(headcell_index);
                cell.setCellStyle(style);
                cell.setCellValue(headers[i]);
                headcell_index++;
            }
            //插入需导出的数据
            for (int i = 0; i < dataList.size(); i++) {
                Row row = sheet.createRow(i + 1);
                row.setHeight((short) (20 * 20)); //设置行高  基数为20
                Object obj = dataList.get(i);
                Class classType = obj.getClass();
                int cell_index=0;
                if(flag){//有序号
                    Cell cell = row.createCell(cell_index);
                    cell.setCellValue(i+1);
                    cell_index=1;
                }
                for (int j = 0; j < fieldNames.length; j++) {
                    Cell cell = row.createCell(cell_index);
                    String fieldName = fieldNames[j];
                    String firstLetter = fieldName.substring(0, 1).toUpperCase();
                    String getMethodName = "get" + firstLetter + fieldName.substring(1);
                    Method getMethod = classType.getMethod(getMethodName, new Class[]{});
                    Object value = getMethod.invoke(obj, new Object[]{});
                    if(value!=null){
                        if (value instanceof Date) {//日期类型
                            String format = "yyyy-MM-dd";
                            if(dateFormats != null && !StringUtils.isEmpty(dateFormats[j])){
                                format = dateFormats[j];
                            }
                            SimpleDateFormat df = new SimpleDateFormat(format);//设置日期格式
                            cell.setCellValue(df.format(value));
                        } else {
                            cell.setCellValue(value.toString());
                        }
                    }
                    cell_index++;
                }
            }
            String agent = request.getHeader("USER-AGENT").toLowerCase();
            //根据浏览器类型处理文件名称
            if (agent.indexOf("msie") > -1) {
                excelFileName = URLEncoder.encode(excelFileName, "UTF-8");
            } else {  //firefox/safari不转码
                excelFileName = new String(excelFileName.getBytes("UTF-8"), "ISO8859-1");
            }
            response.setContentType("application/msexcel");
            response.setHeader("Content-disposition", "attachment;filename=\"" + excelFileName + "\"");
            work.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            logger.error("",e);
        } finally {
            if (out != null){
                out.close();
            }
            if (work != null) {
                work.close();
            }

        }
    }

    public static void createExcel(HttpServletResponse response, HttpServletRequest request, String excelFileName, String sheetName, String[] headers, String[] fieldNames, List dataList, boolean flag) throws IOException {
        createExcel(response,request,excelFileName,sheetName,headers,fieldNames,dataList,flag,null);
    }
    public static void writeExcel(HttpServletResponse response, String fileName, String headers[], String title, List<String[]> dataList, String sheetName) throws IOException {
        OutputStream out = null;
        try {
            XSSFWorkbook work = ExportExcelUtil.createExcelOfUsers( headers, title, sheetName, dataList);
            out = response.getOutputStream();
            response.setContentType("application/ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="
                    .concat(String.valueOf(URLEncoder.encode(fileName , "UTF-8"))));
            work.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            System.out.println("输出流错误");
            logger.error("",e);
        } finally {
            out.close();
        }
    }

    /**
     * 导出excel
     * @param response
     * @param request
     * @param excelFileName 文件名
     * @param sheetName   sheet页名
     * @param headers     表头集合
     * @param fieldNames  表头对应的实体属性集合
     * @param dataList    数据
     * @param flag        第一列是否为序号列
     * @throws IOException
     */
    public static void createExcelDateFormat(HttpServletResponse response, HttpServletRequest request, String excelFileName, String sheetName, String[] headers, String[] fieldNames, List dataList, boolean flag, String[] dateFormats) throws IOException {
        OutputStream out = null;
        XSSFWorkbook work = null;
        try {
            out = response.getOutputStream();
            work = new XSSFWorkbook();
            //创建工作表
            Sheet sheet = work.createSheet(sheetName);
            //显示标题
            Row title_row = sheet.createRow(0);
            title_row.setHeight((short) (40 * 20));
            Row header_row = sheet.createRow(0);
            //创建单元格的 显示样式
            CellStyle style = work.createCellStyle();
            style.setAlignment(CellStyle.ALIGN_CENTER); //水平方向上的对其方式
            style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);  //垂直方向上的对其方式
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 0));
            int headcell_index=0;
            if(flag){//有序号
                sheet.setColumnWidth(headcell_index, 1600);
                Cell headcell = header_row.createCell(headcell_index);
                //应用样式到  单元格上
                headcell.setCellStyle(style);
                headcell.setCellValue("序号");
                headcell_index=1;
            }
            for (int i = 0; i < headers.length; i++) {
                sheet.setColumnWidth(headcell_index, 5000);
                Cell cell = header_row.createCell(headcell_index);
                cell.setCellStyle(style);
                cell.setCellValue(headers[i]);
                headcell_index++;
            }
            //插入需导出的数据
            for (int i = 0; i < dataList.size(); i++) {
                Row row = sheet.createRow(i + 1);
                row.setHeight((short) (20 * 20)); //设置行高  基数为20
                Object obj = dataList.get(i);
                Class classType = obj.getClass();
                int cell_index=0;
                if(flag){//有序号
                    Cell cell = row.createCell(cell_index);
                    cell.setCellValue(i+1);
                    cell_index=1;
                }
                for (int j = 0; j < fieldNames.length; j++) {
                    Cell cell = row.createCell(cell_index);
                    String fieldName = fieldNames[j];
                    String firstLetter = fieldName.substring(0, 1).toUpperCase();
                    String getMethodName = "get" + firstLetter + fieldName.substring(1);
                    Method getMethod = classType.getMethod(getMethodName, new Class[]{});
                    Object value = getMethod.invoke(obj, new Object[]{});
                    if(value!=null){
                        if (value instanceof Date) {//日期类型
                            String format = "yyyy-MM-dd HH:mm";
                            if(dateFormats != null && !StringUtils.isEmpty(dateFormats[j])){
                                format = dateFormats[j];
                            }
                            SimpleDateFormat df = new SimpleDateFormat(format);//设置日期格式
                            cell.setCellValue(df.format(value));
                        } else {
                            cell.setCellValue(value.toString());
                        }
                    }
                    cell_index++;
                }
            }
            response.setContentType("application/msexcel");
            String encodeFileName = URLEncoder.encode(excelFileName, "utf-8");
            response.setHeader("Export-fileName",  encodeFileName);
            work.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            logger.error("",e);
        } finally {
            if (out != null){
                out.close();
            }
            if (work != null){
                work.close();
            }
        }
    }
    public static void createExcelDateFormat2(HttpServletResponse response, String excelFileName, String sheetName, String[] headers, String[] fieldNames, List dataList) throws IOException {
        createExcelDateFormat2(response, null, excelFileName, sheetName, headers, fieldNames, dataList, true, null);
    }
    /**
     * 导出excel
     * @param response
     * @param request
     * @param excelFileName 文件名
     * @param sheetName   sheet页名
     * @param headers     表头集合
     * @param fieldNames  表头对应的实体属性集合
     * @param dataList    数据
     * @param flag        第一列是否为序号列
     * @throws IOException
     */
    public static void createExcelDateFormat2(HttpServletResponse response, HttpServletRequest request, String excelFileName, String sheetName, String[] headers, String[] fieldNames, List dataList, boolean flag, String[] dateFormats) throws IOException {
        OutputStream out = null;
        XSSFWorkbook work = null;
        try {
            out = response.getOutputStream();
            work = new XSSFWorkbook();
            //创建工作表
            Sheet sheet = work.createSheet(sheetName);
            //显示标题
            Row title_row = sheet.createRow(0);
            title_row.setHeight((short) (40 * 20));
            Row header_row = sheet.createRow(0);
            //创建单元格的 显示样式
            CellStyle style = work.createCellStyle();
            style.setAlignment(CellStyle.ALIGN_CENTER); //水平方向上的对其方式
            style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);  //垂直方向上的对其方式
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 0));
            int headcell_index=0;
            if(flag){//有序号
                sheet.setColumnWidth(headcell_index, 1600);
                Cell headcell = header_row.createCell(headcell_index);
                //应用样式到  单元格上
                headcell.setCellStyle(style);
                headcell.setCellValue("序号");
                headcell_index=1;
            }
            for (int i = 0; i < headers.length; i++) {
                sheet.setColumnWidth(headcell_index, 5000);
                Cell cell = header_row.createCell(headcell_index);
                cell.setCellStyle(style);
                cell.setCellValue(headers[i]);
                headcell_index++;
            }
            //插入需导出的数据
            for (int i = 0; i < dataList.size(); i++) {
                Row row = sheet.createRow(i + 1);
                row.setHeight((short) (20 * 20)); //设置行高  基数为20
                Object obj = dataList.get(i);
                Class classType = obj.getClass();
                int cell_index=0;
                if(flag){//有序号
                    Cell cell = row.createCell(cell_index);
                    cell.setCellValue(i+1);
                    cell_index=1;
                }
                for (int j = 0; j < fieldNames.length; j++) {
                    Cell cell = row.createCell(cell_index);
                    String fieldName = fieldNames[j];
                    String firstLetter = fieldName.substring(0, 1).toUpperCase();
                    String getMethodName = "get" + firstLetter + fieldName.substring(1);
                    Method getMethod = classType.getMethod(getMethodName, new Class[]{});
                    Object value = getMethod.invoke(obj, new Object[]{});
                    if(value!=null){
                        if (value instanceof Date) {//日期类型
                            String format = "yyyy-MM-dd HH:mm";
                            if(dateFormats != null && !StringUtils.isEmpty(dateFormats[j])){
                                format = dateFormats[j];
                            }
                            SimpleDateFormat df = new SimpleDateFormat(format);//设置日期格式
                            cell.setCellValue(df.format(value));
                        } else {
                            cell.setCellValue(value.toString());
                        }
                    }
                    cell_index++;
                }
            }
            response.setContentType("application/msexcel");
//            String encodeFileName = URLEncoder.encode(excelFileName, "utf-8");
//            response.setHeader("Export-fileName",  encodeFileName);
            response.setHeader("Content-type", "application/x-xls; charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(excelFileName.getBytes(), "iso-8859-1"));
            work.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            logger.error("",e);
        } finally {
            if (out != null){
                out.close();
            }
            if (work != null){
                work.close();
            }
        }
    }
    /**
     * 导出excel
     * @param sheetName   sheet页名
     * @param headers     表头集合
     * @param fieldNames  表头对应的实体属性集合
     * @param dataList    数据
     * @param flag        第一列是否为序号列
     * @throws IOException
     */
    public static void createExcelDateFormatForOutStream(OutputStream out, String sheetName, String[] headers, String[] fieldNames, List dataList, boolean flag, String[] dateFormats) throws IOException {
        XSSFWorkbook work = null;
        try {
            work = new XSSFWorkbook();
            //创建工作表
            Sheet sheet = work.createSheet(sheetName);
            //显示标题
            Row title_row = sheet.createRow(0);
            title_row.setHeight((short) (40 * 20));
            Row header_row = sheet.createRow(0);
            //创建单元格的 显示样式
            CellStyle style = work.createCellStyle();
            style.setAlignment(CellStyle.ALIGN_CENTER); //水平方向上的对其方式
            style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);  //垂直方向上的对其方式
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 0));
            int headcell_index=0;
            if(flag){//有序号
                sheet.setColumnWidth(headcell_index, 1600);
                Cell headcell = header_row.createCell(headcell_index);
                //应用样式到  单元格上
                headcell.setCellStyle(style);
                headcell.setCellValue("序号");
                headcell_index=1;
            }
            for (int i = 0; i < headers.length; i++) {
                sheet.setColumnWidth(headcell_index, 5000);
                Cell cell = header_row.createCell(headcell_index);
                cell.setCellStyle(style);
                cell.setCellValue(headers[i]);
                headcell_index++;
            }
            //插入需导出的数据
            for (int i = 0; i < dataList.size(); i++) {
                Row row = sheet.createRow(i + 1);
                row.setHeight((short) (20 * 20)); //设置行高  基数为20
                Object obj = dataList.get(i);
                Class classType = obj.getClass();
                int cell_index=0;
                if(flag){//有序号
                    Cell cell = row.createCell(cell_index);
                    cell.setCellValue(i+1);
                    cell_index=1;
                }
                for (int j = 0; j < fieldNames.length; j++) {
                    Cell cell = row.createCell(cell_index);
                    String fieldName = fieldNames[j];
                    String firstLetter = fieldName.substring(0, 1).toUpperCase();
                    String getMethodName = "get" + firstLetter + fieldName.substring(1);
                    Method getMethod = classType.getMethod(getMethodName, new Class[]{});
                    Object value = getMethod.invoke(obj, new Object[]{});
                    if(value!=null){
                        if (value instanceof Date) {//日期类型
                            String format = "yyyy-MM-dd HH:mm";
                            if(dateFormats != null && !StringUtils.isEmpty(dateFormats[j])){
                                format = dateFormats[j];
                            }
                            SimpleDateFormat df = new SimpleDateFormat(format);//设置日期格式
                            cell.setCellValue(df.format(value));
                        } else {
                            cell.setCellValue(value.toString());
                        }
                    }
                    cell_index++;
                }
            }
            work.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            logger.error("",e);
        } finally {
            if (out != null){
                out.close();
            }
            if (work != null){
                work.close();
            }
        }
    }


    //测试
    public static void main(String[] args) throws Exception {

        String headers[] = new String[]{"ID","用户名","年龄","邮箱"};
        String title = "用户详细信息表";
        String sheetName = "用户信息表";

        List<String[]> dataList = new ArrayList<String[]>();
        dataList.add(new String[]{"东邪","17232401001","男","2015年9月"});
        dataList.add(new String[]{"西毒","17232401002","女","2016年9月"});
        dataList.add(new String[]{"南帝","17232401003","男","2017年9月"});
        dataList.add(new String[]{"北丐","17232401004","男","2015年9月"});
        dataList.add(new String[]{"中神通","17232401005","女","2017年9月"});

        createExcelOfUsers(headers,title,sheetName,dataList);
    }

}
